package jrain.fw.dao.mysql;

import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jrain.fw.core.cfg.CfgService;
import jrain.fw.core.dao.SqlParser;
import jrain.fw.core.dao.bean.SqlConst;
import jrain.fw.core.dao.bean.SqlInfo;
import jrain.fw.core.service.Service;
import jrain.fw.core.service.ServiceConst;
import jrain.fw.core.utils.FwUtils;
import jrain.utils.collection.CollectionUtils;
import jrain.utils.exception.ServiceException;
import jrain.utils.lang.StringUtils;
import jrain.utils.sql.SqlUtils;

/**
 * <pre>
 * 作者：489088214@qq.com 
 * 描述：MySQL 语句解析
 * </pre>
 */
@Service(name = "mysql", group = ServiceConst.S_DAO_SQL_PARSER_SERVICE, module = ServiceConst.M_DAO)
public class MySqlSqlParser implements SqlParser {

	private static final Pattern PATTERN = Pattern.compile("#\\{[a-z|_|=|0-9|A-Z]+\\}");
	private final CfgService cfgService;

	public MySqlSqlParser() {
		cfgService = FwUtils.getCfgService();
	}

	@SuppressWarnings("unchecked")
	protected Map<String, Object> getSqlInfo(String namespace, String statement, Map<String, Object> paramData) {
		String key = namespace + "." + statement;
		Object value = cfgService.getCfgValue("sql", key);
		if (value == null) {
			String msg = "There is no corresponding SQL configuration item " + key;
			throw new ServiceException(msg);
		} else {
			return (Map<String, Object>) value;
		}
	}

	public SqlInfo generateInsert(String namespace, String statement, Map<String, Object> paramData) {
		Map<String, Object> sqlInfo = getSqlInfo(namespace, statement, paramData);
		return generateInsert(sqlInfo, paramData);
	}

	@Override
	public SqlInfo generateInsert(Map<String, Object> sqlInfo, Map<String, Object> paramData) {
		SqlInfo sqlObj = new SqlInfo();
		String columnsStr = StringUtils.trimNull(sqlInfo.get("columns"));
		String tableName = StringUtils.trimNull(sqlInfo.get("table"));
		List<Object> rsList = sqlObj.getParams();
		List<String> columns = CollectionUtils.valueOfList(columnsStr);
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO ");
		sql.append(tableName);
		sql.append("(");
		columns.forEach(column -> {
			List<String> fields = CollectionUtils.valueOfList(column, " ");
			String columnCode = column;
			String fieldCode = SqlUtils.columnToField(columnCode);
			if (fields.size() == 2) {
				columnCode = fields.get(0);
				fieldCode = fields.get(1);
			}
			Object value = paramData.get(fieldCode);
			if (value != null) {
				sql.append(columnCode);
				sql.append(",");
				rsList.add(value);
			}
		});
		sql.deleteCharAt(sql.lastIndexOf(","));
		sql.append(")values(");
		sql.append(SqlUtils.createPrepared(rsList.size()));
		sql.append(")");
		sqlObj.setSql(sql.toString());
		return sqlObj;
	}

	public SqlInfo generateUpdate(String namespace, String statement, Map<String, Object> paramData) {
		Map<String, Object> sqlInfo = getSqlInfo(namespace, statement, paramData);
		return generateUpdate(sqlInfo, paramData);
	}

	@Override
	public SqlInfo generateUpdate(Map<String, Object> sqlInfo, Map<String, Object> paramData) {
		SqlInfo sqlObj = new SqlInfo();
		// SQL处理
		String sqlPre = getSqlPre(sqlInfo);
		// 处理SET部分
		StringBuffer setSb = new StringBuffer();
		List<String> columns = CollectionUtils.valueOfList(StringUtils.trimNull(sqlInfo.get("columns")));
		// 处理更新信息
		for (String column : columns) {
			List<String> fields = CollectionUtils.valueOfList(column, " ");
			String columnCode = column;
			String fieldCode = SqlUtils.columnToField(columnCode);
			if (fields.size() == 2) {
				columnCode = fields.get(0);
				fieldCode = fields.get(1);
			}
			Object value = paramData.get(fieldCode);
			if (value != null) {
				setSb.append(columnCode);
				setSb.append(" = ?,");
				sqlObj.addParam(value);
			}
		}
		if (setSb.length() > 0) {
			setSb.deleteCharAt(setSb.lastIndexOf(","));
		}
		sqlPre = sqlPre.replace("#{columns}", setSb.toString());
		String sql = getSqlCondition(sqlInfo, sqlPre, paramData, sqlObj);
		sqlObj.setSql(sql);
		return sqlObj;
	}

	public SqlInfo generateDelete(String namespace, String statement, Map<String, Object> paramData) {
		Map<String, Object> sqlInfo = getSqlInfo(namespace, statement, paramData);
		return generateDelete(sqlInfo, paramData);
	}

	@Override
	public SqlInfo generateDelete(Map<String, Object> sqlInfo, Map<String, Object> paramData) {
		SqlInfo sqlObj = new SqlInfo();
		// SQL处理
		String sqlPre = getSqlPre(sqlInfo);
		// 动态条件
		String sql = getSqlCondition(sqlInfo, sqlPre, paramData, sqlObj);
		sqlObj.setSql(sql);
		return sqlObj;
	}

	public SqlInfo generateQuery(String namespace, String statement, Map<String, Object> paramData) {
		Map<String, Object> sqlInfo = getSqlInfo(namespace, statement, paramData);
		return generateQuery(sqlInfo, paramData);
	}

	@Override
	public SqlInfo generateQuery(Map<String, Object> sqlInfo, Map<String, Object> paramData) {
		SqlInfo sqlObj = new SqlInfo();
		// SQL处理
		String sqlPre = getSqlPre(sqlInfo);
		// 列处理
		String columnsStr = StringUtils.trimNull(sqlInfo.get("columns"), "*");
		List<String> inColumns = SqlUtils.getColumnList(paramData);
		if (inColumns.size() > 0) {
			// 提成不存在字符串
			if (!"*".equals(columnsStr)) {
				List<String> columns = CollectionUtils.valueOfList(columnsStr);
				for (Iterator<String> iterator = inColumns.iterator(); iterator.hasNext();) {
					String column = (String) iterator.next();
					if (!columns.contains(column)) {
						iterator.remove();
					}
				}
			}
			columnsStr = CollectionUtils.joinList(inColumns);
		}
		sqlPre = sqlPre.replace("#{columns}", columnsStr);
		// 动态条件
		String sqlRc = getSqlCondition(sqlInfo, sqlPre, paramData, sqlObj);
		// 排序条件
		String sql = getSqlOrderBy(sqlInfo, sqlRc, paramData, sqlObj);
		sqlObj.setSql(sql);
		// 格式化条件
		@SuppressWarnings("unchecked")
		Map<String, Object> mapper = (Map<String, Object>) sqlInfo.get("foramt");
		sqlObj.setMapper(mapper);
		// 生成统计语句
		String countSql = generateCountSql(sqlRc);
		sqlObj.setCountSql(countSql);
		return sqlObj;
	}

	@Override
	public String generateCountSql(String sql) {
		int fromIndex = sql.indexOf(" from ");
		if (fromIndex == -1) {
			fromIndex = sql.indexOf(" FROM ");
		}
		if (fromIndex == -1) {
			throw new ServiceException("SQL parsing failed, failed to find from ");
		}
		String countAfter = sql.substring(fromIndex);
		countAfter = countAfter.replace("#{OrderBy}", "");
		sql = "SELECT COUNT(1) " + countAfter;
		return sql;
	}

	@Override
	public String generatePageSql(String sql, long startRow, int pageSize) {
		String limit = " limit " + startRow + "," + pageSize;
		return sql + limit;
	}

	protected String getSqlPre(Map<String, Object> sqlInfo) {
		String sql = StringUtils.trimNull(sqlInfo.get("sql"));
		sql = sql.replace("\t", " ");
		sql = sql.replace("\n", " ");
		return sql;
	}

	protected String getSqlCondition(Map<String, Object> sqlInfo, String sqlStr, Map<String, Object> paramData, SqlInfo sqlObj) {
		// 查询条件条件处理
		Matcher conditionMatcher = PATTERN.matcher(sqlStr);
		while (conditionMatcher.find()) {
			String group = conditionMatcher.group();
			String name = group.substring(2, group.length() - 1);
			StringBuffer conditionSql = new StringBuffer();
			boolean b = true;
			if (name.startsWith("condition")) {
				// 动态查询条件
				condition(sqlInfo, paramData, name, conditionSql, sqlObj.getParams(), 1);
			} else if (name.startsWith("AndCondition")) {
				// 动态查询条件
				condition(sqlInfo, paramData, name, conditionSql, sqlObj.getParams(), 1);
			} else if (name.startsWith("OrCondition")) {
				// 动态查询条件
				condition(sqlInfo, paramData, name, conditionSql, sqlObj.getParams(), 2);
			} else if (name.startsWith("OrderBy")) {
				// 动态查询条件
				b = false;
			} else if (name.contains("=")) {
				String newName = StringUtils.trimNull(name);
				String columnCode = "";
				String op = "";
				Object value = null;
				if (newName.startsWith("=")) {
					newName = newName.substring(1);
				} else {
					List<String> fieldColumns = CollectionUtils.valueOfList(name, "=");
					if (fieldColumns.size() == 2) {
						columnCode = fieldColumns.get(0);
						newName = fieldColumns.get(1);
					} else {
						throw new ServiceException("SQL parsing failed, unable to identify" + name);
					}
				}
				if (newName.contains("_")) {
					List<String> fields = CollectionUtils.valueOfList(newName, "_");
					if (fields.size() == 2) {
						op = fields.get(1);
						String fieldName = fields.get(0);
						if (StringUtils.isEmpty(columnCode)) {
							columnCode = SqlUtils.fieldToColumn(fieldName);
						}
						value = paramData.get(fieldName);
					} else {
						throw new ServiceException("SQL parsing failed, unable to identify" + name);
					}
				} else {
					op = "1";
					if (StringUtils.isEmpty(columnCode)) {
						columnCode = SqlUtils.fieldToColumn(newName);
					}
					value = paramData.get(newName);
				}
				// 字段转换
				SqlUtils.parseQueryCondition(columnCode, op, value, conditionSql, sqlObj.getParams(), 3);
			} else if (name.contains("_")) {
				// 值转换
				List<String> fields = CollectionUtils.valueOfList(name, "_");
				if (fields.size() == 2) {
					conditionSql.append("?");
					String fieldName = fields.get(0);
					String columnCode = SqlUtils.fieldToColumn(fieldName);
					String op = fields.get(1);
					Object value = paramData.get(fieldName);
					SqlUtils.parseQueryCondition(columnCode, op, value, new StringBuffer(), sqlObj.getParams(), 3);
				} else {
					throw new ServiceException("SQL parsing failed, unable to identify" + name);
				}
			} else {
				// 值替换
				Object value = paramData.get(name);
				conditionSql.append("?");
				sqlObj.addParam(value);
			}
			if (b) {
				sqlStr = sqlStr.replaceFirst("\\#\\{" + name + "}", conditionSql.toString());
			}
		}
		return sqlStr;
	}

	protected String getSqlOrderBy(Map<String, Object> sqlInfo, String sql, Map<String, Object> paramData, SqlInfo sqlObj) {
		@SuppressWarnings("unchecked")
		Map<String, Object> orderInfo = (Map<String, Object>) sqlInfo.get("order");
		StringBuffer orderSb = new StringBuffer();
		if (orderInfo != null) {
			String orderBy = StringUtils.trimNull(paramData.get(SqlConst.ORDER));
			List<String> list = CollectionUtils.valueOfList(orderBy, ",");
			for (String key : list) {
				List<String> keys = CollectionUtils.valueOfList(key, " ");
				String descOrAsc = "";
				if (keys.size() == 2) {
					key = keys.get(0);
					descOrAsc = keys.get(1);
				}
				String value = StringUtils.trimNull(orderInfo.get(key));
				if (!StringUtils.isEmpty(value)) {
					List<String> values = CollectionUtils.valueOfList(value, " ");
					if (values.size() == 2) {
						value = values.get(0);
						if (StringUtils.isEmpty(descOrAsc)) {
							descOrAsc = values.get(1);
						}
					}
					if (StringUtils.isEmpty(descOrAsc)) {
						descOrAsc = "asc";
					}
					orderSb.append(value);
					orderSb.append(" ");
					orderSb.append(descOrAsc);
					orderSb.append(",");
				}
			}
			if (orderSb.length() > 0) {
				orderSb = orderSb.deleteCharAt(orderSb.lastIndexOf(","));
			} else {
				String orderDefault = StringUtils.trimNull(orderInfo.get("__"));
				if (!StringUtils.isEmpty(orderDefault)) {
					orderSb.append(orderDefault);
				}
			}
			if (orderSb.length() > 0) {
				return sql.replace("#{OrderBy}", "order by " + orderSb.toString());
			}
		}
		sql = sql.replace("#{OrderBy}", "");
		return sql;
	}

	protected void condition(Map<String, Object> sqlInfo, Map<String, Object> paramData, String condition, StringBuffer conditionSql,
			List<Object> conditionData, final int andOr) {
		condition = condition.replace("OrCondition", "");
		condition = condition.replace("AndCondition", "");
		condition = condition.replace("condition", "");
		final String fgroup = StringUtils.trimNull(condition, "__");
		if (andOr == 2) {
			conditionSql.append(" AND (1=0 ");
		}
		boolean badata = false;
		// 字段处理
		Set<String> keySet = paramData.keySet();
		for (String key : keySet) {
			// c_字段名_条件类型_前缀_分组
			if (key.startsWith("c_")) {
				Object value = paramData.get(key);
				String newKey = key.substring(2);
				String[] keyArray = newKey.split("_");
				if (keyArray.length == 2 || keyArray.length == 3 || keyArray.length == 4) {
					// 前缀
					String columnCode = "";
					if (keyArray.length >= 3) {
						columnCode = keyArray[2];
					}
					if (!StringUtils.isEmpty(columnCode)) {
						columnCode = columnCode + ".";
					}
					// 字段名称
					columnCode = columnCode + SqlUtils.fieldToColumn(keyArray[0]);
					columnCode = columnCode.toLowerCase();
					// 条件类型
					String op = keyArray[1];
					// 分组
					String paramGroup = "__";
					if (keyArray.length >= 4) {
						paramGroup = StringUtils.trimNull(keyArray[3], paramGroup);
					}
					if (fgroup.equals(paramGroup)) {
						badata = true;
						SqlUtils.parseQueryCondition(columnCode, op, value, conditionSql, conditionData, andOr);
					}
				}
			}
		}
		if (andOr == 2) {
			if (!badata) {
				conditionSql.append(" or 1=1");
			}
			conditionSql.append(")");
		}
	}

}
